package com.xoa.service.easyReport;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.xoa.dao.easyReport.*;
import com.xoa.model.department.Department;
import com.xoa.model.easyReport.*;
import com.xoa.model.users.Users;
import com.xoa.service.department.DepartmentService;
import com.xoa.util.ExcelUtil;
import com.xoa.util.DateFormat;
import com.xoa.util.ToJson;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

@Service
public class RepStatisticsService {

    @Resource
    DepartmentService departmentService;

    @Resource
    RepFlowMapper repFlowMapper;

    @Resource
    RepDataMapper repDataMapper;

    @Resource
    RepReportMapper repReportMapper;

    @Autowired
    RepFieldMapper repFieldMapper;

    @Autowired
    private RepTableMapper repTableMapper;

    public synchronized ToJson<RepReport> temDatasByTableId(String type, Integer fieldId , Integer tableId, String userId, String startTime , String endTime, String groupBy){
        ToJson<RepReport> toJson = new ToJson<>(1,"err");
        try {
            String fieldStr = "";
            boolean bol = true;
            //1.根据 tableId 和 时间删除部分临时数据
            repReportMapper.deleteByTableIdTime(tableId,startTime,endTime);
            //2.根据 tableId 和 时间查询最新数据然后写入
            List<RepReport> list = new ArrayList<>();
            //查询所有填报的数据
            //写入数据
            for(RepReport repReport : list){
                repReportMapper.insertSelective(repReport);
            }
            //3.查询临时表数据
            List<RepReport> statistics = new ArrayList<>();
            if(!StringUtils.checkNull(type) && "0".equals(type)){
                statistics = repReportMapper.findStatistics(tableId, startTime,endTime,fieldStr);
            }else{
                statistics = repReportMapper.findStatisticsGroup(tableId, startTime,endTime,fieldStr,groupBy);
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObj(statistics);
        } catch (Exception e) {
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    public ToJson<RepReport> exportDatas(HttpServletRequest request, HttpServletResponse response,String type, Integer fieldId , Integer tableId, String userId, String startTime , String endTime, String groupBy){
        ToJson<RepReport> toJson = temDatasByTableId(type,fieldId,tableId,userId,startTime,endTime,groupBy);
        try (ServletOutputStream out = response.getOutputStream();){
            if(toJson.isFlag()){
                //数据
                List<RepReport> list = toJson.getObj();
                HSSFWorkbook workbook = new HSSFWorkbook();
                String filename = "";
                if(!StringUtils.checkNull(type) && "0".equals(type)){
                    filename = "汇总统计表";
                    //表头设置
                    HSSFSheet sheet = workbook.createSheet(filename);
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 24);
                    sheet.addMergedRegion(cellRangeAddress);
                    HSSFRow rowTitle = sheet.createRow(0);
                    HSSFCell cellTitle = rowTitle.createCell(0);
                    cellTitle.setCellValue(filename);
                    //起始列设置
                    String[] secondTitle = {"序号","编码缩写", "学校简称", "人员分类", "总数", "当日发现确诊","当日发现疑似",
                            "核查人数","其中留校","留校中三类","当日从湖北（含途径）返沪人数","目前居家隔离人数","目前集中隔离人数","当日解除隔离人数","当日从其他地区返沪人数",
                            "核查人数","三类2型（密切接触）","2月23日前不返沪人数","核查人数","其中三类","核查人数","其中三类", "失联人数", "更新日期","备注"};
                    HSSFRow rowField ;
                    sheet.createRow(1);
                    sheet.createRow(2);
                    for (int i = 0; i < secondTitle.length; i++) {
                        rowField = sheet.getRow(2);
                        if(i < 7){
                            rowField = sheet.getRow(1);
                            CellRangeAddress cellRangeAddressle = new CellRangeAddress(1, 2, i, i);
                            sheet.addMergedRegion(cellRangeAddressle);
                        }if(i > 21){
                            rowField = sheet.getRow(1);
                            CellRangeAddress cellRangeAddressle = new CellRangeAddress(1, 2, i, i);
                            sheet.addMergedRegion(cellRangeAddressle);
                        }
                        HSSFCell cell = rowField.createCell(i);
                        cell.setCellValue(secondTitle[i]);
                    }
                    //合并头部单元格
                    rowField = sheet.getRow(1);
                    CellRangeAddress cellRangeAddressle1 = new CellRangeAddress(1, 1, 7, 14);
                    CellRangeAddress cellRangeAddressle2 = new CellRangeAddress(1, 1, 15, 17);
                    CellRangeAddress cellRangeAddressle3 = new CellRangeAddress(1, 1, 18, 19);
                    CellRangeAddress cellRangeAddressle4 = new CellRangeAddress(1, 1, 20, 21);
                    sheet.addMergedRegion(cellRangeAddressle1);
                    sheet.addMergedRegion(cellRangeAddressle2);
                    sheet.addMergedRegion(cellRangeAddressle3);
                    sheet.addMergedRegion(cellRangeAddressle4);
                    HSSFCell cell1 = rowField.createCell(7);
                    cell1.setCellValue("在沪人员");
                    HSSFCell cell2 = rowField.createCell(15);
                    cell2.setCellValue("在湖北");
                    HSSFCell cell3 = rowField.createCell(18);
                    cell3.setCellValue("在其它地区");
                    HSSFCell cell4 = rowField.createCell(20);
                    cell4.setCellValue("返校途中");

                    //填写数据
                    String oldSchoolName = null;
                    int x = 1;
                    boolean is = true;
                    for(int i = 0 ;i < list.size() ;i++){
                        boolean iss = false;
                        boolean bol = true;
                        RepReport repReport = list.get(i);
                        Map map = BeanUtils.describe(repReport);
                        HSSFRow rowFields  = sheet.createRow(i+3);
                        String schoolName = repReport.getSchoolName();
                        for (int j = 0; j < secondTitle.length; j++) {
                            HSSFCell cell = rowFields.createCell(j);
                            //第一个学校处理
                            if(i == 0){
                                //设置序号
                                if(j == 0){
                                    cell.setCellValue((x));
                                    oldSchoolName = schoolName;
                                }
                                //设置编码缩写
                                if(j == 1)
                                    cell.setCellValue(repReport.getSchoolNo().substring(repReport.getSchoolNo().length()-4));
                                //设置学校简称
                                if(j == 2)
                                    cell.setCellValue(schoolName);
                            }else if(schoolName.equals(oldSchoolName) && bol && is){
                                if(j<3){
                                    CellRangeAddress cellAddresses = new CellRangeAddress(i+2, i+3, j, j);
                                    sheet.addMergedRegion(cellAddresses);
                                }
                                iss = true;
                            }else{
                                //设置序号
                                if(j == 0){
                                    cell.setCellValue((++x));
                                    oldSchoolName = schoolName;
                                    bol = false;
                                    is = true;
                                }
                                cell.setCellValue((x));
                                //设置编码缩写
                                if(j == 1)
                                    cell.setCellValue(repReport.getSchoolNo().substring(repReport.getSchoolNo().length()-4));
                                //设置学校简称
                                if(j == 2)
                                    cell.setCellValue(schoolName);
                            }

                            if(j >2 && j< 23)
                                cell.setCellValue((String) map.get("data"+j));
                            if(j == 23){
                                cell.setCellValue(DateFormat.getDatestr(repReport.getFlowDate()));
                            }
                            if(j == 24){
                                cell.setCellValue((String) map.get("data23"));
                            }
                        }
                        if(iss)is=false;
                    }

                    filename = "汇总统计表.xls";
                    filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                }else {
                    //按类型导出
                    filename = "学校类型汇总统计表";
                    //表头设置
                    HSSFSheet sheet = workbook.createSheet(filename);
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 20);
                    sheet.addMergedRegion(cellRangeAddress);
                    HSSFRow rowTitle = sheet.createRow(0);
                    HSSFCell cellTitle = rowTitle.createCell(0);
                    cellTitle.setCellValue(filename);
                    //起始列设置
                    String[] secondTitle = {"序号", "学校类别", "总数", "当日发现确诊","当日发现疑似",
                            "核查人数","其中留校","留校中三类","当日从湖北（含途径）返沪人数","目前居家隔离人数","目前集中隔离人数","当日解除隔离人数","当日从其他地区返沪人数",
                            "核查人数","三类2型（密切接触）","2月23日前不返沪人数","核查人数","其中三类","核查人数","其中三类", "失联人数"};
                    HSSFRow rowField ;
                    sheet.createRow(1);
                    sheet.createRow(2);
                    for (int i = 0; i < secondTitle.length; i++) {
                        rowField = sheet.getRow(2);
                        if(i < 5){
                            rowField = sheet.getRow(1);
                            CellRangeAddress cellRangeAddressle = new CellRangeAddress(1, 2, i, i);
                            sheet.addMergedRegion(cellRangeAddressle);
                        }if(i > 19){
                            rowField = sheet.getRow(1);
                            CellRangeAddress cellRangeAddressle = new CellRangeAddress(1, 2, i, i);
                            sheet.addMergedRegion(cellRangeAddressle);
                        }
                        HSSFCell cell = rowField.createCell(i);
                        cell.setCellValue(secondTitle[i]);
                    }
                    //合并头部单元格
                    rowField = sheet.getRow(1);
                    CellRangeAddress cellRangeAddressle1 = new CellRangeAddress(1, 1, 5, 12);
                    CellRangeAddress cellRangeAddressle2 = new CellRangeAddress(1, 1, 13, 15);
                    CellRangeAddress cellRangeAddressle3 = new CellRangeAddress(1, 1, 16, 17);
                    CellRangeAddress cellRangeAddressle4 = new CellRangeAddress(1, 1, 18, 19);
                    sheet.addMergedRegion(cellRangeAddressle1);
                    sheet.addMergedRegion(cellRangeAddressle2);
                    sheet.addMergedRegion(cellRangeAddressle3);
                    sheet.addMergedRegion(cellRangeAddressle4);
                    HSSFCell cell1 = rowField.createCell(5);
                    cell1.setCellValue("在沪人员");
                    HSSFCell cell2 = rowField.createCell(13);
                    cell2.setCellValue("在湖北");
                    HSSFCell cell3 = rowField.createCell(16);
                    cell3.setCellValue("在其它地区");
                    HSSFCell cell4 = rowField.createCell(18);
                    cell4.setCellValue("返校途中");

                    //填写数据
                    for(int i = 0 ;i < list.size() ;i++){
                        RepReport repReport = list.get(i);
                        Map map = BeanUtils.describe(repReport);
                        HSSFRow rowFields  = sheet.createRow(i+3);
                        String schoolType = repReport.getSchoolType();
                        for (int j = 0; j < secondTitle.length; j++) {
                            HSSFCell cell = rowFields.createCell(j);
                            //设置序号
                            if(j == 0){
                                cell.setCellValue((i+1));
                            }
                            //设置学校类型
                            if(j == 1)
                                cell.setCellValue(schoolType);
                            if(j > 1 )
                                cell.setCellValue((String) map.get("data"+(j+2)));
                        }
                    }
                    filename = "学校类型汇总统计表.xls";
                    filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                }
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("content-disposition", "attachment;filename=" + filename);
                workbook.write(out);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return toJson;
    }


    /**
     * 传入对象json ，
     * @param request
     * @param json
     * @return
     */
    public ToJson findReportCondition(HttpServletRequest request, String json) {
        ToJson toJson=new ToJson(1,"暂无数据");
        try {
            Map map=new HashMap();
            Map maps =json!=null ? (Map) JSON.parse(json) : new HashMap();
            if (maps.size()==0){
                return toJson;
            }
            map.put("tableId",maps.get("tableId"));
            map.put("startTime",maps.get("startTime"));
            map.put("endTime",maps.get("endTime"));

            //要添加到 rep_report 的数据
            List<Map<String,Object>> list=new ArrayList();
            //根据 tableId 和 时间查询 rep_flow 最新数据然后写入
            //查询所有填报的数据
            List<Map<String,Object>> repFlowList = repFlowMapper.findTableTime(map);
            for (Map<String, Object> repFlow:repFlowList) {
                Map repReport=new HashMap();
                //主键
                repReport.put("FLOW_ID",repFlow.get("FLOW_ID"));
                repReport.put("REP_TABLE_ID",maps.get("tableId"));
                if(repFlow.get("DEPT_ID") != null){
                    //存放填报人的一级部门Id,学校ID
                    Department dept_id = departmentService.getFatherDept((Integer) repFlow.get("DEPT_ID"));
                    repReport.put("SCHOOL_ID",dept_id.getDeptId());
                }
                //报表日期
                repReport.put("FLOW_DATE",repFlow.get("FLOW_TIME"));

                //查询填报数据表 rep_data
                map.put("flowId",repFlow.get("FLOW_ID"));
                List<Map<String,Object>> repDataList = repDataMapper.findByFlowId(map);
                for (Map<String, Object> repData:repDataList) {
                    //等于2为数值类型
                    if (!StringUtils.checkNull(repData.get("FIELD_TYPE").toString())
                            && repData.get("FIELD_TYPE").toString().equals("2")
                            && !StringUtils.checkNull(repData.get("DATA").toString())
                            && repData.get("DATA").toString().length() <10){
                        int i = Integer.parseInt(repData.get("DATA").toString());
                        repReport.put("data"+repData.get("FIELD_ID"),i);
                    }else{
                        repReport.put("data"+repData.get("FIELD_ID"),repData.get("DATA"));
                    }
                }
                list.add(repReport);
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObj(list); //值
            //toJson.setObject(repFieldMapper.selectByTableId(Integer.parseInt((String)maps.get("tableId")))); //列
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }


    /**
     * 查询第一个字段
     * @param request
     * @param tableId
     * @return
     */
    public ToJson findminfield(HttpServletRequest request, Integer tableId) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map map=new HashMap();
            map.put("tableId",tableId);
            Map<String, Object> stringObjectMap = repFlowMapper.findTableTime(map).get(0);
            map.put("flowId",stringObjectMap.get("FLOW_ID"));
            //查询 rep_data 表
            List<Map<String, Object>> byFlowId = repDataMapper.findByFlowId(map);
            if (byFlowId.size()>0){
                toJson.setMsg("true");
                toJson.setObject(byFlowId.get(0)); //最小值
                toJson.setFlag(0);
            }
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    /**
     * 导出通用汇总
     * @param request
     * @param tableId
     * @param startTime
     * @param endTime
     * @param template
     * @return
     */
    public ToJson ExportReportCondition(HttpServletRequest request, HttpServletResponse response, String tableId, String startTime, String endTime, boolean template,String flag) {
        ToJson toJson=new ToJson(1,"false");
        OutputStream out = null;
        try {
            Map map=new HashMap();
            map.put("tableId",tableId);
            map.put("startTime",startTime);
            map.put("endTime",endTime);
            List<Map<String,Object>> obj=new ArrayList<>();
            if (template){
                String json= JSONObject.toJSONString(map);
                ToJson reportCondition = this.findReportCondition(request, json);
                obj =(List<Map<String,Object>>) reportCondition.getObj();
            }
            List<RepField> object =repFieldMapper.selectByTableId(Integer.parseInt((String)map.get("tableId")));
            RepTable repTable = repTableMapper.selectByPrimaryKey(Integer.parseInt(tableId));

            List<String> secondTitles=new ArrayList<>();

            List<String> beanProperty=new ArrayList<>();
            for (RepField repField:object) {
                if (!StringUtils.checkNull(repField.getFieldName())) {
                    secondTitles.add(repField.getFieldName());
                }
                if (!StringUtils.checkNull(repField.getFieldId().toString())) {
                    beanProperty.add("data"+repField.getFieldId());
                }
            }

            HSSFWorkbook tableWork = new HSSFWorkbook();
            makeSecondHead(tableWork,  secondTitles.toArray(new String[secondTitles.size()]));
            if (template){
                exportExcelData(tableWork, obj, beanProperty.toArray(new String[beanProperty.size()]));
            }
            response.setContentType("text/html;charset=UTF-8");
            out = response.getOutputStream();
            String filename = FileUtils.encodeDownloadFilename(repTable.getRepTableName()+".xls",request.getHeader("user-agent"));
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-disposition","attachment;filename=" + filename);
            tableWork.write(out);
            out.flush();
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }finally {
            try {
                if(out!=null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return toJson;
    }


    public  HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){
        // 创建用户属性栏
        HSSFSheet sheet = workbook.createSheet("sheet1");// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFRow rowField = sheet.createRow(0); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFCellStyle styleField = ExcelUtil.createStyle(workbook, (short)13);
        for (int i = 0; i < secondTitles.length; i++) {
            HSSFCell cell = rowField.createCell(i);
            cell.setCellValue(secondTitles[i]);
            cell.setCellStyle(styleField);
        }
        return workbook;
    }


    public <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<Map<String,Object>> dataList, String[] beanPropertys) throws Exception {
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 填充数据
        HSSFCellStyle styleData = workbook.createCellStyle();
        styleData.setAlignment(HorizontalAlignment.CENTER);
        styleData.setVerticalAlignment(VerticalAlignment.CENTER);
        ////设置单元格格式为"文本"
        HSSFDataFormat format = workbook.createDataFormat();
        styleData.setDataFormat(format.getFormat("@"));
        for (int j = 0; j < dataList.size(); j++) {
            HSSFRow rowData = sheet.createRow(j + 1);
            Map<String, Object> stringObjectMap1 = dataList.get(j);
            stringObjectMap1.put("NUMBER",j+1);
            for(int k=0; k<beanPropertys.length; k++){
                Object value = stringObjectMap1.get(beanPropertys[k]);
                HSSFCell cellData = rowData.createCell(k);
                try {
                    if (value!=null && !value.equals("")) {
                        if(value instanceof Integer ){
                            cellData.setCellValue(Integer.parseInt(value.toString()));
                        }else{
                            cellData.setCellValue(value.toString());
                        }
                    }else {
                        cellData.setCellValue("");

                    }
                    cellData.setCellStyle(styleData);
                    cellData.setCellType(CellType.STRING);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }


    /**
     * 通用汇总导入
     * @param request
     * @param file
     * @return
     */
    public ToJson ImportReportCondition(HttpServletRequest request, MultipartFile file, String json) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map maps =json!=null ? (Map) JSON.parse(json) : new HashMap();
            if (maps.size()==0){
                return toJson;
            }
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            String fileName = file.getOriginalFilename();
            if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                InputStream in = file.getInputStream();
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
                // 循环工作表Sheet，工作表只有一个可以不加循环
                HSSFSheet hssfSheet = this.getLastRowNum(hssfWorkbook);
                if (hssfSheet!=null){
                    List<RepField> repFields = repFieldMapper.selectByTableId(Integer.parseInt((String) maps.get("tableId")));
                    // 循环行Row,按照每一行进行新增
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

                        // 添加填报信息表
                        RepFlow repFlow=new RepFlow();
                        repFlow.setRepTableId(Integer.parseInt((String) maps.get("tableId")));
                        repFlow.setUserId(user.getUserId());
                        repFlow.setFlowTime(new Date());
                        repFlowMapper.insertSelective(repFlow);

                        // 添加 填报数据表
                        List<Map<String,Object>> mapList=new ArrayList<>();
                        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                        int column= hssfSheet.getRow(rowNum).getPhysicalNumberOfCells();
                        for (int i=0;i<column;i++) {
                            HSSFCell cell = hssfRow.getCell(i);
                            cell.setCellType(CellType.STRING);
                            String stringCellValue = cell.getStringCellValue();
                            if (StringUtils.checkNull(stringCellValue)){
                                continue;
                            }
                            Map map=new HashMap();
                            map.put("FLOW_ID",repFlow.getFlowId());
                            map.put("FIELD_ID",repFields.get(i).getFieldId());
                            map.put("DATA",stringCellValue);
                            mapList.add(map);
                        }
                        //新增rep_data
                        if (!mapList.isEmpty()){
                            repReportMapper.insertThrough(mapList);
                        }
                    }
                }
            }else{
                toJson.setMsg("请导入.xls或.xlsx格式的文件");
            }
            toJson.setMsg("true");
            toJson.setFlag(0);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }


    public static HSSFSheet getLastRowNum(HSSFWorkbook hssfWorkbook){
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            int column= hssfSheet.getRow(rowNum).getPhysicalNumberOfCells();
            for (int i=0;i<column;i++) {
                HSSFCell cell = hssfRow.getCell(i);
                cell.setCellType(CellType.STRING);
                String stringCellValue = cell.getStringCellValue();
                if (StringUtils.checkNull(stringCellValue)){
                    hssfSheet.removeRow(hssfRow);
                    break;
                }
            }
        }
        return hssfSheet;
    }

}
